package dao;
import entity.EquipmentBean;
import entity.Page;
import utils.DBUtil;
import utils.DBUtil_Plus;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class EquipmentDAO {
    /**
     * 查询满足条件的任务列表
     * @param eq_number 产品编号
     * @return List<EquipmentBean>--符合条件的任务列表
     */
//    public List<EquipmentBean> findEquipment(String eq_number,String eq_producer,String eq_data,String eq_after_sale,String eq_type) throws SQLException {
//        List<EquipmentBean> taskList = new ArrayList<EquipmentBean>();
//        Connection conn = DBUtil_Plus.getConnection();
//        String selectSql = "select * from eq_list where 1 = 1";
//        String sql = "";
//        if (eq_number != null && !"".equals(eq_number)) {
//            sql += " and eq_number like ?";
//        }
//        if (eq_producer != null && !"".equals(eq_producer)) {
//            sql += " and eq_producer like ?";
//        }
//        if (eq_data != null && !"".equals(eq_data)) {
//            sql += " and eq_data like ?";
//        }
//        if (eq_after_sale != null && !"".equals(eq_after_sale)) {
//            sql += " and eq_after_sale like ?";
//        }
//        if (eq_type != null && !"".equals(eq_type)) {
//            sql += " and eq_type = ?";
//        }
//        // 组装计数的sql语句
//        PreparedStatement pstmt = conn.prepareStatement(selectSql + sql);
//        int paramIndex = 1;
//        if (eq_number != null && !"".equals(eq_number)) {
//            pstmt.setString(paramIndex++, "%" + eq_number + "%");
//        }
//        if (eq_producer != null && !"".equals(eq_producer)) {
//            pstmt.setString(paramIndex++, "%" + eq_producer + "%");
//        }
//        if (eq_data != null && !"".equals(eq_data)) {
//            pstmt.setString(paramIndex++, "%" + eq_data + "%");
//        }
//        if (eq_after_sale != null && !"".equals(eq_after_sale)) {
//            pstmt.setString(paramIndex++, "%" + eq_after_sale + "%");
//        }
//        if (eq_type != null && !"".equals(eq_type)) {
//            pstmt.setString(paramIndex, eq_type);
//        }
//        ResultSet rs = pstmt.executeQuery();
//        while (rs.next()) {
//            EquipmentBean obj = new EquipmentBean();
//            obj.setEq_id(rs.getInt("id"));
//            obj.setEq_name(rs.getString("eq_name"));
//            obj.setEq_number(rs.getString("eq_number"));
//            obj.setEq_type(rs.getString("eq_type"));
//            obj.setEq_producer(rs.getString("eq_producer"));
//            obj.setEq_data(rs.getString("eq_data"));
//            obj.setEq_store_data(rs.getString("eq_store_data"));
//            obj.setEq_after_sale(rs.getString("eq_after_sale"));
//            obj.setEq_phone(rs.getString("eq_phone"));
//            obj.setEq_introduction(rs.getString("eq_introduction"));
//            taskList.add(obj);
//        }
//        return taskList;
//    }
    public List<EquipmentBean> findEquipment(String eq_number, String eq_producer, String eq_data, String eq_after_sale, String eq_type, Page page) throws SQLException {
        List<EquipmentBean> taskList = new ArrayList<EquipmentBean>();
        String selectSql = "select * from eq_list where 1 = 1";
        String sql = "";
        if (!eq_number.isEmpty())
            sql += " and eq_number like ?";
        if (!eq_producer.isEmpty())
            sql += " and eq_producer like ?";
        if (!eq_data.isEmpty())
            sql += " and eq_data like ?";
        if (!eq_after_sale.isEmpty())
            sql += " and eq_after_sale like ?";
        if (!eq_type.isEmpty())
            sql += " and eq_type like ?";
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;

        conn = DBUtil_Plus.getConnection();

        // 查询满足条件的总记录数
        String countQuery = "SELECT CEIL(COUNT(*)/?) AS totalPages FROM (" + selectSql+" "+sql + ") AS sub";
        pstmt = conn.prepareStatement(countQuery);
        pstmt.setInt(1, page.getPageSize());

        int paramIndex = 1;

        if (!eq_number.isEmpty()) {
            eq_number = "%" + eq_number + "%";
            paramIndex++;
            pstmt.setString(paramIndex, eq_number);
        }
        if (!eq_producer.isEmpty()) {
            eq_producer = "%" + eq_producer + "%";
            paramIndex++;
            pstmt.setString(paramIndex, eq_producer);
        }
        if (!eq_data.isEmpty()) {
            paramIndex++;
            pstmt.setString(paramIndex, eq_data);
        }
        if (!eq_after_sale.isEmpty()) {
            eq_after_sale = "%" + eq_after_sale + "%";
            paramIndex++;
            pstmt.setString(paramIndex, eq_after_sale);
        }
        if (!eq_type.isEmpty()) {
            eq_type = "%" + eq_type + "%";
            paramIndex++;
            pstmt.setString(paramIndex, eq_type);
        }

        rs = pstmt.executeQuery();

        // 提取总页数
        if (rs.next()) {
            page.setTotalPage(rs.getInt("totalPages"));
        }

        // 添加排序和分页
        String finalSql = selectSql+" "+sql + " ORDER BY id LIMIT " + page.getCurrentPageTotalRows() + ", " + page.getPageSize();
        pstmt = conn.prepareStatement(finalSql);

        paramIndex = 0;
        if (!eq_number.isEmpty()) {
            eq_number = "%" + eq_number + "%";
            paramIndex++;
            pstmt.setString(paramIndex, eq_number);
        }
        if (!eq_producer.isEmpty()) {
            eq_producer = "%" + eq_producer + "%";
            paramIndex++;
            pstmt.setString(paramIndex, eq_producer);
        }
        if (!eq_data.isEmpty()) {
            paramIndex++;
            pstmt.setString(paramIndex, eq_data);
        }
        if (!eq_after_sale.isEmpty()) {
            eq_after_sale = "%" + eq_after_sale + "%";
            paramIndex++;
            pstmt.setString(paramIndex, eq_after_sale);
        }
        if (!eq_type.isEmpty()) {
            eq_type = "%" + eq_type + "%";
            paramIndex++;
            pstmt.setString(paramIndex, eq_type);
        }
        // 执行查询
        rs = pstmt.executeQuery();

        while (rs.next()) {
            EquipmentBean obj = new EquipmentBean();
            obj.setEq_id(rs.getInt("id"));
            obj.setEq_name(rs.getString("eq_name"));
            obj.setEq_number(rs.getString("eq_number"));
            obj.setEq_type(rs.getString("eq_type"));
            obj.setEq_producer(rs.getString("eq_producer"));
            obj.setEq_data(rs.getString("eq_data"));
            obj.setEq_store_data(rs.getString("eq_store_data"));
            obj.setEq_after_sale(rs.getString("eq_after_sale"));
            obj.setEq_phone(rs.getString("eq_phone"));
            obj.setEq_introduction(rs.getString("eq_introduction"));
            taskList.add(obj);
        }
        rs.close();
        pstmt.close();
        DBUtil_Plus.closeConnection(conn);
        return taskList;
    }
    /**
     * @param id 根据id去删除指定的值
     * @return true or false
     * @throws SQLException
     */
    public boolean  deleteEquipmentById(String id) throws SQLException {
        if (id != null && !id.trim().isEmpty()) {
            int delete_id = Integer.parseInt(id);
            Connection conn = DBUtil_Plus.getConnection();
            // 获取执行sql的对象Statement
            Statement stmt = conn.createStatement();
            String sql = "delete from eq_list where id = " + delete_id + " ";
            int rs = stmt.executeUpdate(sql);
            // UPDATE eq_list SET id = id - 1 WHERE id > 1;
            // 创建一条数据操纵语句，如 UPDATE 或 DELETE
            String sql1 = "UPDATE eq_list set id =  id - 1 where id > " + delete_id + " ";
            stmt.executeUpdate(sql1);
            DBUtil_Plus.closeConnection(conn);
            return true;
        } else {
            return false;
        }
    }

    /**
     *
     * @param eq_id
     * @param eq_name
     * @param eq_number
     * @param eq_type
     * @param eq_producer
     * @param eq_data
     * @param eq_store_data
     * @param eq_after_sale
     * @param eq_phone
     * @param eq_introduction
     * @return true or false 在这里是可以优化的
     * @throws SQLException
     */
    public boolean addEquipmentListOrEdit(String eq_id,String eq_name,String eq_number,String eq_type,String eq_producer,String eq_data,String eq_store_data,
    String eq_after_sale,String eq_phone,String eq_introduction) throws SQLException {
        Connection conn = DBUtil_Plus.getConnection();
        if(eq_id.equals("null")){
            // 这里用的是Request的请求方式 所以获取到的是String类型的
            // 上面忘记转换了这里就需要这样来判断
            // 这里的eq_id是字符串的null String eq_id = "null" 而不是String eq_id = null 可能是因为
            // 在页面中传递了两次，第一次是第二种情况，但是再传递一个的话就是第一种情况了
            int eq_idd;
            String query = "SELECT id FROM eq_list ORDER BY id DESC LIMIT 1";
            PreparedStatement pstmt_query = conn.prepareStatement(query);
            ResultSet rs_query = pstmt_query.executeQuery();
            if(rs_query.next()){
                eq_idd = rs_query.getInt("id");
                eq_idd++;
            }else{
                return false;
            }
            String sql1  = "insert into eq_list (id,eq_name,eq_number,eq_type,eq_producer,eq_data,eq_store_data,eq_after_sale,eq_phone,eq_introduction) values (?,?,?,?,?,?,?,?,?,?)";
            PreparedStatement pstmt1 = conn.prepareStatement(sql1);
            pstmt1.setInt(1,eq_idd);
            pstmt1.setString(2,eq_name);
            pstmt1.setString(3,eq_number);
            pstmt1.setString(4,eq_type);
            pstmt1.setString(5,eq_producer);
            pstmt1.setString(6,eq_data);
            pstmt1.setString(7,eq_store_data);
            pstmt1.setString(8,eq_after_sale);
            pstmt1.setString(9,eq_phone);
            pstmt1.setString(10,eq_introduction);
            int rs = pstmt1.executeUpdate();
            return  true;
        }else{
            String update_message = "update eq_list set eq_name = ?,eq_number = ?,eq_type = ?,eq_producer = ?,eq_data = ?,eq_store_data = ?,eq_after_sale = ?,eq_phone = ?,eq_introduction = ? where id = ?";
            PreparedStatement pstmt = conn.prepareStatement(update_message);
            pstmt.setString(1,eq_name);
            pstmt.setString(2,eq_number);
            pstmt.setString(3,eq_type);
            pstmt.setString(4,eq_producer);
            pstmt.setString(5,eq_data);
            pstmt.setString(6,eq_store_data);
            pstmt.setString(7,eq_after_sale);
            pstmt.setString(8,eq_phone);
            pstmt.setString(9,eq_introduction);
            pstmt.setInt(10, Integer.parseInt(eq_id));
            int rs = pstmt.executeUpdate();
            return true;
        }
    }

    /**
     *
     * @param id
     * @return 返回对象EquipmentBean用于记录上次的查询记录
     * @throws SQLException
     */
    public EquipmentBean fillFormMessage(int id) throws SQLException {
        EquipmentBean list_add = new EquipmentBean();
        if (id != 0) {
            int update_id = id;
            Connection conn = DBUtil_Plus.getConnection();
            // 获取执行sql的对象Statement
            Statement stmt = conn.createStatement();
            String sql = "select * from eq_list where id =  " + update_id + " ";
            ResultSet rs = stmt.executeQuery(sql);
            rs.next();
            list_add.setEq_name(rs.getString("eq_name"));
            list_add.setEq_number(rs.getString("eq_number"));
            list_add.setEq_type(rs.getString("eq_type"));
            list_add.setEq_producer(rs.getString("eq_producer"));
            list_add.setEq_data(rs.getString("eq_data"));
            list_add.setEq_after_sale(rs.getString("eq_after_sale"));
            list_add.setEq_phone(rs.getString("eq_phone"));
            list_add.setEq_store_data(rs.getString("eq_store_data"));
            list_add.setEq_introduction(rs.getString("eq_introduction"));
            rs.close();
            stmt.close();
            DBUtil_Plus.closeConnection(conn);
            return list_add;
        } else {
            return list_add;
        }
    }
}
